package cn.com.shopec.erp.common.utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang.ArrayUtils;
import org.apache.log4j.Logger;

/**
 * JDBC操作简单工具类 获取连接，执行连接
 */

public class JDBCUtils {

    private static final Logger logger = Logger.getLogger(JDBCUtils.class);

    private static JDBCUtils jdbcUtils = null;

    private String jdbc_driver = "oracle.jdbc.driver.OracleDriver"; // jdbc驱动

    private String jdbc_url = "jdbc:oracle:thin:@192.168.6.121:1521:szscm";
    // //jdbc连接Url
    private String user_name = "szscm"; // jdbc连接用户名

    private String user_password = "szscm"; // jdbc连接密码

    private String jdbc_batch_size = "50"; // 批量提交数
    
    private JDBCUtils() {
	
    }

    public void initProperty(Map<String, String> map) throws Exception {
    	if(null!=map){
    		this.jdbc_driver = map.get("jdbc_driver");
    		this.jdbc_url = map.get("jdbc_url");
    		this.user_name = map.get("user_name");
    		this.user_password = map.get("user_password");
    		this.jdbc_batch_size  = map.get("jdbc_batch_size");
    	}
    }

    /**
     * 创建JDBC工具类实例
     * 
     * @return
     */
    public static synchronized JDBCUtils getInstance() {

        if (jdbcUtils == null) {
            jdbcUtils = new JDBCUtils();
        }
        return jdbcUtils;
    }

    /**
     * 获取 数据库连接
     * 
     * @return
     * @throws Exception
     */
    public Connection getConnection() throws Exception {
        try {
            
            Class.forName(this.jdbc_driver);
            Connection conn = DriverManager.getConnection(this.jdbc_url, this.user_name, this.user_password);
            return conn;

        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e);
            throw new Exception();
        }
    }
  

    /**
     * 增加单条数据
     * 
     * @param sql
     *            sql语句
     * @param values
     *            参数值
     * @return 是否增加成功
     * @throws Exception
     */
    public boolean saveOrUpdate(String sql, Object... values) throws Exception {
        Connection conn = this.getConnection(); // 获取数据库连接
        PreparedStatement pstmt = null;
        try {
            conn.setAutoCommit(false); // 设置手动提交事务
            pstmt = conn.prepareStatement(sql); // 创建PreparedStatement对象
            // 赋值
            if (null != values) {
                for (int i = 0; i < values.length; i++) {
                    pstmt.setObject(i + 1, values[i]);
                }
            }
            pstmt.execute(); // 执行操作
            conn.commit(); // 提交事务
            this.close(pstmt, conn); // 关闭相关连接
        } catch (SQLException e) {
            e.printStackTrace();
            logger.error(e);
            throw new Exception();
        } finally {
            this.close(pstmt, conn); // 关闭相关连接
        }
        return true;
    }

    /**
     * 删除
     * 
     * @param sql
     * @return
     * @throws Exception
     */
    public boolean batchDelete(String sql) {
        Connection conn = null; // 获取数据库连接
        PreparedStatement pstmt = null;
        try {
            conn = this.getConnection();
            conn.setAutoCommit(false); // 设置手动提交事务
            pstmt = conn.prepareStatement(sql); // 创建PreparedStatement对象

            pstmt.execute(); // 执行操作
            conn.commit(); // 提交事务
            this.close(pstmt, conn); // 关闭相关连接
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e);
            return false;
        } finally {
            this.close(pstmt, conn); // 关闭相关连接
        }
        return true;

    }

    /**
     * 批量增加与修改
     * 
     * @param sql
     *            insert or update 语句
     * @param params
     *            参数集合
     * @return
     * @throws Exception
     * @throws Exception
     * @throws SQLException
     */
    public boolean batchSaveOrUpdate(String sql, List<Object[]> paramList) {
        PreparedStatement pstmt = null;
        int count = 0;
        Connection conn = null;
        try {
            conn = this.getConnection(); // 获取数据库连接
            count = Integer.parseInt(this.jdbc_batch_size) - 1;
            conn.setAutoCommit(false); // 设置手动提交事务
            pstmt = conn.prepareStatement(sql); // 创建PreparedStatement对象
            // 赋值
            for (int i = 0; i < paramList.size(); i++) {

                Object[] values = paramList.get(i);
                for (int j = 0; j < values.length; j++) {
                    pstmt.setObject(j + 1, values[j]);
                }
                pstmt.addBatch();

                // 批量数等于 batch_size 时 提交数据
                if (i != 0 && (i % count == 0)) {
                    int ids[] = pstmt.executeBatch(); // 执行操作
                    if (ids.length == count + 1) {
                        conn.commit(); // 提交事务
                    } else {
                        conn.rollback(); // 事务回滚
                    }
                    pstmt.clearBatch();
                }
            }

            int ids[] = pstmt.executeBatch(); // 执行操作
            if (ids.length == paramList.size() % (count + 1)) {
                conn.commit(); // 提交事务
            } else {
                conn.rollback(); // 事务回滚
            }
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e);
            return false; // 如果异常就返回false
        } finally {
            this.close(pstmt, conn); // 关闭相关连接
        }
        return true;
    }

    /**
     * 批量更新 or 保存
     * 
     * @param map
     * @return
     * @throws Exception
     */
    public boolean batchAllSaveOrUpdate(Map<String, List<Object[]>> map) throws Exception {
        if (map == null || map.size() == 0) {
            return false;
        }
        // int count = Integer.parseInt(batch_size)-1;
        Connection conn = this.getConnection(); // 获取数据库连接
        PreparedStatement ps = null;
        try {
            conn.setAutoCommit(false);
            for (Iterator<Entry<String, List<Object[]>>> iter = map.entrySet().iterator(); iter.hasNext();) {
                Entry<String, List<Object[]>> entry = iter.next();
                // 动态获取preparedstatement对象
                ps = conn.prepareStatement(entry.getKey());
                for (int i = 0; i < entry.getValue().size(); i++) {
                    Object[] obj = entry.getValue().get(i);
                    for (int j = 0; j < obj.length; j++) {
                        ps.setObject(j + 1, obj[j]);
                    }
                    ps.addBatch();
                    if (i == entry.getValue().size() - 1) {
                        int[] num = ps.executeBatch();
                        if (num.length != entry.getValue().size()) {
                            throw new Exception("执行的数据记录不相等");
                        }
                        ps.clearBatch();
                    }
                }
            }
            conn.commit();
        } catch (SQLException e) {
            logger.error(e);
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
                logger.error(e1);
                return false;
            }
            e.printStackTrace();
            logger.error(e);
            return false;
        } finally {
            this.close(ps, conn);
        }
        return true;
    }

    /**
     * 批量更新库存预占数量
     * 
     * @param map
     * @return
     * @throws Exception
     */
    public boolean batchUpdateInventoryPreemptedQuantity(Map<String, List<Object[]>> map) throws Exception {
        if (map == null || map.size() == 0) {
            return false;
        }
        // int count = Integer.parseInt(batch_size)-1;
        Connection conn = this.getConnection(); // 获取数据库连接
        PreparedStatement ps = null;
        try {
            conn.setAutoCommit(false);
            for (Iterator<Entry<String, List<Object[]>>> iter = map.entrySet().iterator(); iter.hasNext();) {
                Entry<String, List<Object[]>> entry = iter.next();
                // 动态获取preparedstatement对象
                ps = conn.prepareStatement(entry.getKey());
                for (int i = 0; i < entry.getValue().size(); i++) {
                    Object[] obj = entry.getValue().get(i);
                    for (int j = 0; j < obj.length; j++) {
                        ps.setObject(j + 1, obj[j]);
                    }
                    ps.addBatch();
                    if (i == entry.getValue().size() - 1) {
                        int[] num = ps.executeBatch();
                        if (num.length != entry.getValue().size()) {
                            throw new Exception("执行的数据记录不相等");
                        }
                        ps.clearBatch();
                    }
                }
            }

            conn.commit();
        } catch (SQLException e) {
            logger.error(e);
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
                logger.error(e1);
                return false;
            }
            e.printStackTrace();
            return false;
        } finally {
            this.close(ps, conn);
        }
        return true;
    }

    /**
     * 
     * 方法描述：在调用处提交事务
     * 
     * @param conn
     * @param map
     * @return
     * @throws Exception
     */
    public boolean batchAllSaveOrUpdate(Connection conn, Map<String, List<Object[]>> map) throws SQLException {
        logger.info("批量JDBC开始---------------------------------------");
        boolean success = false;
        if (map == null || map.size() == 0) {
            return false;
        }
        PreparedStatement ps = null;
        try {
            conn.setAutoCommit(false);
            for (Iterator<Entry<String, List<Object[]>>> iter = map.entrySet().iterator(); iter.hasNext();) {
                Entry<String, List<Object[]>> entry = iter.next();
                // 动态获取preparedstatement对象
                ps = conn.prepareStatement(entry.getKey());
                for (int i = 0; i < entry.getValue().size(); i++) {
                    Object[] obj = entry.getValue().get(i);
                    for (int j = 0; j < obj.length; j++) {
                        ps.setObject(j + 1, obj[j]);
                    }
                    ps.addBatch();
                    if (i == entry.getValue().size() - 1) {
                        int[] num = ps.executeBatch();
                        if (num.length != entry.getValue().size()) {
                            throw new SQLException("执行的数据记录不相等");
                        }
                        ps.clearBatch();
                    }
                }
            }
            // conn.commit();
            logger.info("批量JDBC" + success + "--");
            success = true;
            logger.info("批量JDBC" + success + "--");
        } catch (SQLException e) {
            logger.error("batchAllSaveOrUpdate批量JDBC" + e.getMessage());
            e.printStackTrace();
        } finally {
            if (null != ps) {
                ps.close();
            }
            logger.info("状态为--" + success);
        }
        logger.info("批量JDBC结束---------------------------------------");
        return success;
    }

    /**
     * 批量增加与修改
     * 
     * @param sql
     *            insert or update 语句
     * @param params
     *            参数集合
     * @return
     * @throws Exception
     * @throws Exception
     * @throws Exception
     * @throws SQLException
     */
    public void executebatchSaveOrUpdate( Connection conn,String sql, List<Object[]> paramList) throws Exception {
       
    	PreparedStatement pstmt = null;
        int count = 0;
     
            count = Integer.parseInt(this.jdbc_batch_size) - 1;
            pstmt = conn.prepareStatement(sql); // 创建PreparedStatement对象
            // 赋值
            for (int i = 0; i < paramList.size(); i++) {

                Object[] values = paramList.get(i);
                for (int j = 0; j < values.length; j++) {
                    pstmt.setObject(j + 1, values[j]);
                }
                pstmt.addBatch();

                // 批量数等于 batch_size 时 提交数据
                if (i != 0 && (i % count == 0)) {
                    pstmt.executeBatch(); // 执行操作      
                    pstmt.clearBatch();
                    pstmt.close();
                    pstmt = null;
                    pstmt = conn.prepareStatement(sql);
                }
            }
            pstmt.executeBatch(); // 执行操作       
            pstmt.clearBatch();
            pstmt.close();
            pstmt = null;
    }
    
    /**
     * 批量增加与修改
     * 
     * @param sql
     *            insert or update 语句
     * @param params
     *            参数集合
     * @return
     * @throws Exception
     * @throws Exception
     * @throws Exception
     * @throws SQLException
     */
    public boolean batchOffPayUpdate(String sql, List<Object[]> paramList) throws Exception {
        PreparedStatement pstmt = null;
        int count = 0;
        Connection conn = null;
        try {
            conn = this.getConnection(); // 获取数据库连接
            count = Integer.parseInt(this.jdbc_batch_size) - 1;
            conn.setAutoCommit(false); // 设置手动提交事务
            pstmt = conn.prepareStatement(sql); // 创建PreparedStatement对象
            // 赋值
            for (int i = 0; i < paramList.size(); i++) {

                Object[] values = paramList.get(i);
                for (int j = 0; j < values.length; j++) {
                    pstmt.setObject(j + 1, values[j]);
                }
                pstmt.addBatch();

                // 批量数等于 batch_size 时 提交数据
                if (i != 0 && (i % count == 0)) {
                    int ids[] = pstmt.executeBatch(); // 执行操作
                    if (ids.length == count + 1) {
                        conn.commit(); // 提交事务
                    } else {
                        conn.rollback(); // 事务回滚
                    }
                    pstmt.clearBatch();
                }
            }

            int ids[] = pstmt.executeBatch(); // 执行操作
            if (ids.length == paramList.size() % (count + 1)) {
                conn.commit(); // 提交事务
            } else {
                conn.rollback(); // 事务回滚
            }

        } catch (SQLException e) {
            logger.error(e);
            e.printStackTrace();
            conn.rollback();
            throw new Exception();
        } catch (Exception e) {
            logger.error(e);
            e.printStackTrace();
            conn.rollback();
            throw new Exception();
        } finally {
            this.close(pstmt, conn); // 关闭相关连接
        }
        return true;
    }

    public String replaceString(String s) {
        return s.replace("[", "").replace("]", "");
    }

    /**
     * 执行存储过程 方法描述
     * 
     * @throws Exception
     */
    public boolean executecallprocedure(String supplyid) throws Exception {
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            // conn=WmsConnectionInitproperty(BaseDataManagerConstant.AREA_JDBC_DRIVER,BaseDataManagerConstant.AREA_JDBC_URL,BaseDataManagerConstant.AREA_JDBC_USERNAME,BaseDataManagerConstant.AREA_JDBC_PASSWORD);
            // //获取数据库连接
            conn = this.getConnection();
            conn.setAutoCommit(false);
            pstmt = conn.prepareCall("{call sp_update_area_warehouse_inventory (?)}");// 执行存储过程
            pstmt.setString(1, supplyid);
            pstmt.executeUpdate();
            // String sql = "delete from area_warehouse_inventory";
            // deletewarehouseinventory(sql, pstmt, conn);
            conn.commit();
        } catch (Exception e) {
            logger.error(e);
            conn.rollback();
            throw e;
        } finally {
            this.close(pstmt, conn);
        }
        return true;
    }

    public void deletewarehouseinventory(String sql, PreparedStatement pstmt, Connection conn) throws Exception {
        try {
            pstmt = conn.prepareStatement(sql);// 将库存记录表数据清空
            pstmt.executeUpdate();
        } catch (Exception e) {
            logger.error(e);
            e.printStackTrace();
        }
    }

    /**
     * jdbc查询
     * 
     * @param sql
     *            sql语句
     * @return
     * @throws Exception
     */
    public ResultSet queryList(String sql) {
        Connection conn = null; // 获取数据库连接
        PreparedStatement pstmt = null;
        ResultSet resultSet = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql); // 创建PreparedStatement对象
            resultSet = pstmt.executeQuery();
        } catch (Exception e) {
            logger.error(e);
            e.printStackTrace();
        } finally {
            this.close(pstmt, conn); // 关闭相关连接
        }
        return resultSet;
    }

    /**
     * 
     * 方法描述：统计查询结果集行数
     * 
     * @param sql
     * @return int
     */
    public int count(String sql) {
        int result = 0;
        Connection conn = null; // 获取数据库连接
        PreparedStatement pstmt = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql); // 创建PreparedStatement对象
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                result = rs.getInt(1);
            }
        } catch (Exception e) {
            logger.error(e);
            e.printStackTrace();
        } finally {
            this.close(pstmt, conn); // 关闭相关连接
        }
        return result;
    }

    /**
     * 
     * 方法描述：统计查询结果集行数
     * 
     * @param sql
     * @return int
     */
    public int count(String sql, Object[] objects) {
        int result = 0;
        Connection conn = null; // 获取数据库连接
        PreparedStatement pstmt = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql); // 创建PreparedStatement对象
            for (int j = 0; j < objects.length; j++) {
                pstmt.setObject(j + 1, objects[j]);
            }
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                result = rs.getInt(1);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            this.close(pstmt, conn); // 关闭相关连接
        }
        return result;
    }

    /**
     * 批量增加与修改
     * 
     * @param sql
     *            insert or update 语句
     * @param params
     *            参数集合
     * @return
     * @throws Exception
     * @throws SQLException
     */
    public boolean batchSaveOrUpdate(String sql, List<Object[]> paramList, long timestamp) throws Exception {
        boolean flag = false;
        int count = Integer.parseInt(this.jdbc_batch_size) - 1;
        Connection conn = this.getConnection(); // 获取数据库连接
        PreparedStatement pstmt = null;
        try {
            conn.setAutoCommit(false); // 设置手动提交事务
            pstmt = conn.prepareStatement(sql); // 创建PreparedStatement对象
            // 赋值
            for (int i = 0; i < paramList.size(); i++) {

                Object[] values = paramList.get(i);
                for (int j = 0; j < values.length; j++) {
                    pstmt.setObject(j + 1, values[j]);
                }
                pstmt.setObject(values.length + 1, timestamp++);
                pstmt.addBatch();

                // 批量数等于 batch_size 时 提交数据
                if (i != 0 && (i % count == 0)) {
                    int ids[] = pstmt.executeBatch(); // 执行操作
                    if (ids.length == count + 1) {
                        conn.commit(); // 提交事务
                    } else {
                        conn.rollback(); // 事务回滚
                    }
                    pstmt.clearBatch();
                }
            }

            int ids[] = pstmt.executeBatch(); // 执行操作
            if (ids.length == paramList.size() % (count + 1)) {
                conn.commit(); // 提交事务
                flag = true;
            } else {
                conn.rollback(); // 事务回滚
            }
        } catch (SQLException e) {
            logger.error(e);
            e.printStackTrace();
        } finally {
            this.close(pstmt, conn); // 关闭相关连接
        }
        return flag;
    }

    /**
     * 查询返回单一结果
     * 
     * @param sql
     * @return
     */
    public Object uniqueResult(String sql) {
        return this.uniqueResult(sql, null);
    }

    public Object uniqueResult(String sql, Object[] values) {

        Object result = null;
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = this.getConnection();
            pstmt = conn.prepareStatement(sql);
            if (ArrayUtils.isEmpty(values)) {
                for (int j = 0; j < values.length; j++) {
                    pstmt.setObject(j + 1, values[j]);
                }
            }
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                result = rs.getObject(1);
            }
        } catch (Exception e) {
            logger.error(e);
            e.printStackTrace();
        } finally {
            this.close(pstmt, conn);
        }

        return result;
    }

    /**
     * 批理执行更新
     * 
     * @param sqlBatchs
     * @return boolean
     */
    public boolean executeBatch(Map<String, List<Object[]>> sqlBatchs) {
        boolean result = false;
        if (MapUtils.isEmpty(sqlBatchs)) {
            return result;
        }

        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = this.getConnection();
            conn.setAutoCommit(false);
            int rowCount = 0;
            int batchCount = Integer.parseInt(this.jdbc_batch_size);
            for (Map.Entry<String, List<Object[]>> entry : sqlBatchs.entrySet()) {
                pstmt = conn.prepareStatement(entry.getKey());
                rowCount = 0;
                for (Object[] objects : entry.getValue()) {
                    for (int i = 0; i < objects.length; i++) {
                        pstmt.setObject(i + 1, objects[i]);
                    }
                    pstmt.addBatch();
                    if (++rowCount % batchCount == 0) {
                        pstmt.executeBatch();
                    }
                }
                if (rowCount % batchCount != 0) {
                    pstmt.executeBatch();
                }
            }
            conn.commit();
            result = true;
        } catch (Exception e) {
            logger.error(e);
            try {
                conn.rollback();
            } catch (SQLException ex) {
                logger.error(e);
            }
        } finally {
            logger.info(sqlBatchs);
            this.close(conn, pstmt);
        }
        return result;
    }

    /**
     * 批理执行更新
     * 
     * @param sqlBatchs
     * @return boolean
     */
    public boolean executeBatch(List<SQLBatch> sqlBatchs) {

        boolean result = false;
        if (CollectionUtils.isEmpty(sqlBatchs)) {
            return result;
        }

        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = this.getConnection();
            conn.setAutoCommit(false);
            int rowCount = 0;
            int batchCount = Integer.parseInt(this.jdbc_batch_size);
            Collections.sort(sqlBatchs);
            for (SQLBatch sqlBatch : sqlBatchs) {
                pstmt = conn.prepareStatement(sqlBatch.getSql());
                rowCount = 0;
                for (Object[] objects : sqlBatch.getSqlParams()) {
                    for (int i = 0; i < objects.length; i++) {
                        pstmt.setObject(i + 1, objects[i]);
                    }

                    pstmt.addBatch();
                    if (++rowCount % batchCount == 0) {
                        pstmt.executeBatch();
                    }
                }

                if (rowCount % batchCount != 0) {
                    pstmt.executeBatch();
                }
            }

            conn.commit();
            result = true;

        } catch (Exception e) {
            logger.error(e);
            try {
                conn.rollback();
            } catch (SQLException ex) {
                logger.error(e);
            }
        } finally {
            logger.info(sqlBatchs);
            this.close(conn, pstmt);
        }

        return result;
    }

    /**
     * 商品价格段批量更新
     * 
     * @param sqlBatchs
     * @return
     */
    public boolean executeBatchOfCommodity(List<SQLBatch> sqlBatchs) {
        boolean result = false;
        if (sqlBatchs == null || sqlBatchs.isEmpty()) {
            return result;
        }
        Connection conn = null;
        PreparedStatement pstmt = null;
        try {
            conn = this.getConnection();
            conn.setAutoCommit(false);
            Collections.sort(sqlBatchs);
            for (SQLBatch sqlBatch : sqlBatchs) {
                pstmt = conn.prepareStatement(sqlBatch.getSql());
                pstmt.addBatch();
                pstmt.executeBatch();
            }
            conn.commit();
            result = true;
        } catch (Exception e) {
            logger.error(e);
            try {
                conn.rollback();
            } catch (SQLException ex) {
                logger.error(e);
            }
        } finally {
            logger.info(sqlBatchs);
            try {
                if (pstmt != null) {
                    pstmt.close();
                    pstmt = null;
                }
            } catch (SQLException e) {
                logger.error(e);
            }

            try {
                if (conn != null) {
                    if (!conn.isClosed()) {
                        conn.close();
                    }
                    conn = null;
                }
            } catch (SQLException e) {
                logger.error(e);
            }
        }
        return result;
    }

    /**
     * 关闭数据库相关连接
     * 
     * @param connection
     */
    public void close(ResultSet rs, Statement st, Connection conn) {
        try {
            if (rs != null) {
                rs.close();
            }
            rs = null;
        } catch (SQLException e) {
            e.printStackTrace();
            logger.error(e);
        } finally {
            try {
                if (st != null) {
                    st.close();
                }
                st = null;
            } catch (SQLException e) {
                e.printStackTrace();
                logger.error(e);
            } finally {
                try {
                    if (conn != null) {
                        conn.close();
                    }
                    conn = null;
                } catch (SQLException e) {
                    e.printStackTrace();
                    logger.error(e);
                }
            }
        }
    }

    /**
     * 关闭数据库相关连接
     * 
     * @param connection
     */
    public void close(PreparedStatement pstmt, Connection conn) {
        try {
            if (pstmt != null) {
                pstmt.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
            logger.error(e);
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
                logger.error(e);
            }
        }
    }

    private void close(Connection conn, Statement pstmt) {
        this.close(conn, pstmt, null);
    }

    private void close(Connection conn, Statement pstmt, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
                rs = null;
            }
        } catch (SQLException e) {
            logger.error(e);
        }
        try {
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
        } catch (SQLException e) {
            logger.error(e);
        }

        try {
            if (conn != null) {
                if (!conn.isClosed()) {
                    conn.close();
                }
                conn = null;
            }
        } catch (SQLException e) {
            logger.error(e);
        }
    }

    public static final class SQLBatch implements Comparable<SQLBatch> {

        private Integer priority;// 优先级
        private String sql;// SQL语句
        private List<Object[]> sqlParams;// SQL语句参数

        public SQLBatch(Integer priority, String sql, List<Object[]> sqlParams) {
            this.priority = priority;
            this.sql = sql;
            this.sqlParams = sqlParams;
        }

        public Integer getPriority() {
            return this.priority;
        }

        public String getSql() {
            return this.sql;
        }

        public List<Object[]> getSqlParams() {
            return this.sqlParams;
        }

        @Override
        public int compareTo(SQLBatch o) {
            return this.priority.compareTo(o.priority);
        }

        @Override
        public String toString() {
            StringBuilder sb = new StringBuilder();
            sb.append(" SQLBatch { priority = ").append(this.priority).append(", sql = ").append(this.sql);
            for (int i = this.sqlParams.size() - 1; i >= 0; i--) {
                sb.append(", sqlParams[").append(i).append("] = ").append(Arrays.toString(this.sqlParams.get(i)));
            }
            return sb.append(" } ").toString();
        }
    }

    public static void main(String[] args) throws SQLException {
        JDBCUtils utils = JDBCUtils.getInstance();

        String sql = "insert into t_order_log "
                + " (id,order_sub_id,serviceType,functionType,logInfo,createtime,operatorType,operatorCode,operateResult,remark) "
                + " values (?,?,?,?,?,?,?,?,?,?) ;";
        List<Object[]> paramList = new ArrayList<Object[]>();

        for (int i = 0; i < 10; i++) {
            Object[] param = new Object[] { "id_" + String.valueOf(System.currentTimeMillis()), "order_sub_id_" + i,
                    "serviceType_" + i, "functionType_" + i, "logInfo_" + i, Long.valueOf(System.currentTimeMillis()),
                    "operatorType_" + i, "operatorCode_" + i, "operateResult_" + i, "remark_" + i };
            paramList.add(param);
        }

        utils.batchSaveOrUpdate(sql, paramList);

    }

    public String getJdbc_driver() {
        return this.jdbc_driver;
    }

    public void setJdbc_driver(String jdbc_driver) {
        this.jdbc_driver = jdbc_driver;
    }

    public String getJdbc_url() {
        return this.jdbc_url;
    }

    public void setJdbc_url(String jdbc_url) {
        this.jdbc_url = jdbc_url;
    }

    public String getUser_name() {
        return this.user_name;
    }

    public void setUser_name(String user_name) {
        this.user_name = user_name;
    }

    public String getUser_password() {
        return this.user_password;
    }

    public void setUser_password(String user_password) {
        this.user_password = user_password;
    }

    public String getJdbc_batch_size() {
        return this.jdbc_batch_size;
    }

    public void setJdbc_batch_size(String jdbc_batch_size) {
        this.jdbc_batch_size = jdbc_batch_size;
    }

}
